﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Honestysoft.Baseframework.WebUI;
using Honestysoft.Financing.Model;
using System.IO;
using Honestysoft.Financing.WebUI.Module.Audit;

namespace Honestysoft.Financing.WebUI.Apply
{
    public partial class test : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btn1_Click(object sender, EventArgs e)
        {
            string sn = txtSN.Text;

            sn = sn.Replace("（", "(").Replace("）", ")");
            if (sn.EndsWith(")"))//有括号
            {
                string lsn = ""; //前半部分
                string rsn = "";
                lsn = sn.Substring(0, sn.IndexOf("("));
                rsn = sn.Substring(sn.IndexOf("("));
                txtResult.Text = "F" + lsn.PadLeft(3, '0') + rsn;
            }
            else
            {
                txtResult.Text = "F" + sn.PadLeft(3, '0');
            }

            //if (sn.EndsWith(")") || sn.EndsWith("）"))//有括号
            //{
            //    string lsn = ""; //前半部分
            //    string rsn = "";
            //    if (sn.EndsWith(")"))
            //    {
            //        lsn = sn.Substring(0, sn.IndexOf("("));
            //        rsn = sn.Substring(sn.IndexOf("("));
            //    }
            //    else
            //    {
            //        lsn = sn.Substring(0, sn.IndexOf("（"));
            //        rsn = sn.Substring(sn.IndexOf("（"));
            //    }
            //    txtResult.Text = "F" + lsn.PadLeft(3, '0') + rsn;
            //}
            //else
            //{
            //    txtResult.Text = "F" + sn.PadLeft(3, '0');
            //}
        }



        /// <summary>
        /// 设置字体为红色
        /// </summary>
        /// <param name="excelHelper"></param>
        /// <param name="rowIndex"></param>
        /// <param name="colIndex"></param>
        /// <param name="cellText"></param>
        private void SetRedColor(Honestysoft.BaseLibrary.Common.ExcelHelper excelHelper, int rowIndex, int columnIndex, string cellText)
        { 
            //先设置为全部黑色(X)xxx(Y)yyy(Z)zzz
            //Excel中字符的索引是从1开始
            excelHelper.FontStyle(1, rowIndex, columnIndex, 1, cellText.Length, BaseLibrary.Common.ColorIndex.黑色);
            int s = 0, e = 0;
            char[] arrChar = cellText.ToCharArray();
            for (int i = 0; i < arrChar.Length; i++)
            {
                if (arrChar[i].Equals('('))
                {
                    s = i;
                }
                if (arrChar[i].Equals(')'))
                {
                    e = i;
                }
                if (s != 0 && e != 0 && s < e)
                {
                    excelHelper.FontStyle(1, rowIndex, columnIndex, s + 1, e - s + 1, BaseLibrary.Common.ColorIndex.红色);
                    s = 0;
                    e = 0;
                }
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string templateFileName = Server.MapPath(Globals.Home + "Module/Audit/ExcelTemplate/AT_A.xlsx");
            string id = txtA.Text;
            Bu_AuditTable atObj = new Honestysoft.Financing.BusinessLogic.Audit(Globals.CurrentConnStringName).GetAuditTable(id);
            string outFilePath = Globals.Home + "Reports/" + atObj.Code + ".xlsx";
            string outPutFile = Server.MapPath(outFilePath);
            if (File.Exists(outPutFile))
            {
                File.Delete(outPutFile);
            }
            Honestysoft.BaseLibrary.Common.ExcelHelper excelHelper = new BaseLibrary.Common.ExcelHelper(templateFileName, outPutFile);

            try
            {
                #region 表头
                //表头、一、
                //编号
                excelHelper.SetCells(1, 2, 8, atObj.Code);
                //项目名称
                excelHelper.SetCells(1, 4, 4, atObj.ProjectName);
                //项目代码
                excelHelper.SetCells(1, 4, 7, atObj.ProjectCode);
                //项目单位
                excelHelper.SetCells(1, 5, 4, atObj.Owner971Name);
                //建设单位
                excelHelper.SetCells(1, 5, 7, atObj.OwnerName);
                //合同名称
                excelHelper.SetCells(1, 6, 4, atObj.ContractName);
                //施工单位
                excelHelper.SetCells(1, 7, 4, atObj.ContractorName);
                //资料接收日期
                if (atObj.DocRecTime.HasValue)
                {
                    excelHelper.SetCells(1, 7, 7, Honestysoft.Financing.WebUI.Module.Audit.AuditUtils.FormatRecDate(atObj.DocRecTime.Value));
                }
                //收款银行
                excelHelper.SetCells(1, 8, 4, atObj.Bank);
                //收款账号
                excelHelper.SetCells(1, 8, 7, atObj.BankAccount);
                #endregion
                int totalAddCount = 0;//增加的总行数
                int startRowIndex = 0, curRowIndex = 0;//动态行开始行号，当前行号                
                string cellText = "";
                #region 二、本项目有关数据及统计核查
                #region 本合同本期申请资金金额：A
                //11行开始，如果同时申请的还有2笔，则是11、12、13行
                cellText = string.Format("本笔申请是本期第({0})笔", atObj.TableNo);
                excelHelper.SetCells(1, 11, 5, cellText);
                SetRedColor(excelHelper, 11, 5, cellText);
                excelHelper.SetCells(1, 11, 8, atObj.Amount_S.ToString());
                excelHelper.FontStyle(11, 8, 11, 8, true, false, BaseLibrary.Common.UnderlineStyle.无下划线);
                //开始动态新增行
                startRowIndex = 11;
                curRowIndex = startRowIndex;
                //本期同时申请的其他笔
                for (int i = 0; i < atObj.CurOther_C.Count; i++)
                {
                    cellText = string.Format("本期同时申请的第({0})笔金额", atObj.CurOther_C[i].TableNo);
                    excelHelper.InsertRows(1, curRowIndex + 1, 1);
                    excelHelper.MergeCells(1, curRowIndex + 1, 5, curRowIndex + 1, 7, cellText, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                    SetRedColor(excelHelper, curRowIndex + 1, 5, cellText);
                    excelHelper.SetCells(1, curRowIndex + 1, 8, atObj.CurOther_C[i].Amount_S.ToString());

                    curRowIndex++;
                    totalAddCount++;
                }
                excelHelper.FontStyle(startRowIndex + 1, 8, curRowIndex, 8, false, false, BaseLibrary.Common.UnderlineStyle.无下划线);
                //合并“1、”
                excelHelper.MergeCells(1, startRowIndex, 2, curRowIndex, 2, "1、");
                //合并“本合同本期申请资金金额：A”
                cellText = "本合同本期申请资金金额：A";
                excelHelper.MergeCells(1, startRowIndex, 3, curRowIndex, 4, cellText, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                excelHelper.FontStyle(1, startRowIndex, 3, cellText.Length, 1, true, false, BaseLibrary.Common.UnderlineStyle.无下划线);
                #endregion
                #region 本合同前期投控公司的累计融资金额： B
                startRowIndex = 12 + totalAddCount;
                curRowIndex = startRowIndex;
                cellText = string.Format("({0})笔已拨付金额累计", atObj.SummaryAmount.count_C_A);
                excelHelper.SetCells(1, startRowIndex, 5, cellText);
                SetRedColor(excelHelper, startRowIndex, 5, cellText);
                excelHelper.SetCells(1, startRowIndex, 6, atObj.SummaryAmount.TAmount_C_A == 0 ? "" : atObj.SummaryAmount.TAmount_C_A.ToString());
                if (atObj.SummaryAmount.TAmount_C_A_Date.HasValue)
                {
                    excelHelper.SetCells(1, startRowIndex, 7, string.Format("{0:截止到yyyy/M/d}", atObj.SummaryAmount.TAmount_C_A_Date.Value));
                }
                //合同未拨付明细
                for (int i = 0; i < atObj.PastTime_C_NA.Count; i++)
                {
                    excelHelper.InsertRows(1, curRowIndex + 1, 1);
                    excelHelper.SetCells(1, curRowIndex + 1, 6, atObj.PastTime_C_NA[i].ActiveAmount == 0 ? "" : atObj.PastTime_C_NA[i].ActiveAmount.ToString());
                    excelHelper.SetCells(1, curRowIndex + 1, 7, atObj.PastTime_C_NA[i].AuditTableCode.ToString());
                    curRowIndex++;
                    totalAddCount++;
                }
                //合并“2、”
                excelHelper.MergeCells(1, startRowIndex, 2, curRowIndex, 2, "2、");
                //合并“本合同前期投控公司的累计融资金额：B”
                cellText = "本合同前期投控公司的累计融资金额：B";
                excelHelper.MergeCells(1, startRowIndex, 3, curRowIndex, 4, cellText, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                excelHelper.FontStyle(1, startRowIndex, 3, cellText.Length, 1, true, false, BaseLibrary.Common.UnderlineStyle.无下划线);
                //合并“合同未拨付明细”
                cellText = string.Format("({0})笔未拨付金额明细", atObj.SummaryAmount.count_C_NA);
                excelHelper.MergeCells(1, startRowIndex + 1, 5, curRowIndex, 5, cellText, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                SetRedColor(excelHelper, startRowIndex + 1, 5, cellText);
                //合并合同已拨付加未拨付
                excelHelper.MergeCells(1, startRowIndex, 8, curRowIndex, 8, atObj.SummaryAmount.TAmount_C_NA_A.ToString(), Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                #endregion
                #region 本合同前期加本期融资金额累计： C=A+B
                excelHelper.MergeCells(1, 13 + totalAddCount, 5, 13 + totalAddCount, 7, "", Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                excelHelper.SetCells(1, 13 + totalAddCount, 8, atObj.SummaryAmount.TAmount_C.ToString());
                #endregion
                #region 本项目本期申请资金合计： D（本项目所有合同本期资金申请之和)
                startRowIndex = 14 + totalAddCount;
                curRowIndex = startRowIndex;
                excelHelper.SetCells(1, startRowIndex, 6, atObj.CurOther_P[0].Amount_S.ToString());
                excelHelper.SetCells(1, startRowIndex, 7, atObj.CurOther_P[0].AuditTableCode);
                //本项目本期所有记录
                for (int i = 1; i < atObj.CurOther_P.Count; i++)
                {
                    excelHelper.InsertRows(1, curRowIndex + 1, 1);
                    excelHelper.SetCells(1, curRowIndex + 1, 6, atObj.CurOther_P[i].Amount_S == 0 ? "" : atObj.CurOther_P[i].Amount_S.ToString());
                    excelHelper.SetCells(1, curRowIndex + 1, 7, atObj.CurOther_P[i].AuditTableCode);
                    curRowIndex++;
                    totalAddCount++;
                }
                //合并“4、”
                excelHelper.MergeCells(1, startRowIndex, 2, curRowIndex, 2, "4、", Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                //合并“本项目本期申请资金合计：D”
                excelHelper.MergeCells(1, startRowIndex, 3, curRowIndex, 4, "本项目本期申请资金合计：D\n（本项目所有合同本期资金申请之和)", Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                excelHelper.FontStyle(1, startRowIndex, 3, cellText.IndexOf("D") + 1, 1, true, false, BaseLibrary.Common.UnderlineStyle.无下划线);
                //合并“已报审”
                cellText = string.Format("本项目已报审({0})个合同，本期有({1})个合同申请({2})笔资金", atObj.SummaryAmount.count_PC_All, atObj.SummaryAmount.count_PC, atObj.SummaryAmount.count_PT);
                excelHelper.MergeCells(1, startRowIndex, 5, curRowIndex, 5, cellText, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                SetRedColor(excelHelper, startRowIndex, 5, cellText);
                //合并本期所有
                excelHelper.MergeCells(1, startRowIndex, 8, curRowIndex, 8, atObj.SummaryAmount.TAmount_P_NA_A.ToString(), Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                #endregion
                #region 本项目前期投控公司的累计融资金额： E
                //项目已拨付金额
                cellText = string.Format("({0})笔已拨付金额累计", atObj.SummaryAmount.count_P_A);
                startRowIndex = 15 + totalAddCount;
                curRowIndex = startRowIndex;
                excelHelper.SetCells(1, curRowIndex, 5, cellText);
                SetRedColor(excelHelper, curRowIndex, 5, cellText);
                excelHelper.SetCells(1, curRowIndex, 6, atObj.SummaryAmount.TAmount_P_A == 0 ? "" : atObj.SummaryAmount.TAmount_P_A.ToString());
                if (atObj.SummaryAmount.TAmount_P_A_Date.HasValue)
                {
                    excelHelper.SetCells(1, curRowIndex, 7, string.Format("截止到{0:yyyy/M/d}", atObj.SummaryAmount.TAmount_P_A_Date.Value));
                }
                //项目未拨付
                for (int i = 0; i < atObj.PastTime_P_NA.Count; i++)
                {
                    excelHelper.InsertRows(1, curRowIndex + 1, 1);
                    excelHelper.SetCells(1, curRowIndex + 1, 6, atObj.PastTime_P_NA[i].ActiveAmount == 0 ? "" : atObj.PastTime_P_NA[i].ActiveAmount.ToString());
                    excelHelper.SetCells(1, curRowIndex + 1, 7, atObj.PastTime_P_NA[i].AuditTableCode.ToString());
                    curRowIndex++;
                    totalAddCount++;
                }
                //合并“5、”
                excelHelper.MergeCells(1, startRowIndex, 2, curRowIndex, 2, "5、");
                //合并“本项目前期投控公司的累计融资金额： E”
                cellText = "本项目前期投控公司的累计融资金额： E\n（2011年度投控拨付的本项目所有合同前期资金之和）";
                excelHelper.MergeCells(1, startRowIndex, 3, curRowIndex, 4, cellText, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                excelHelper.FontStyle(1, startRowIndex, 3, cellText.IndexOf("E") + 1, 1, true, false, BaseLibrary.Common.UnderlineStyle.无下划线);
                cellText = string.Format("({0})笔未拨付金额明细", atObj.SummaryAmount.count_P_NA);
                excelHelper.MergeCells(1, startRowIndex + 1, 5, curRowIndex, 5, cellText, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                SetRedColor(excelHelper, startRowIndex + 1, 5, cellText);
                //合并项目未拨付加已拨付
                excelHelper.MergeCells(1, startRowIndex, 8, curRowIndex, 8, atObj.SummaryAmount.TAmount_P_NA_A.ToString(), Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                #endregion
                #region 本项目本期加前期融资资金累计 F=D+E
                excelHelper.SetCells(1, 16 + totalAddCount, 8, atObj.SummaryAmount.TAmount_P.ToString());
                excelHelper.FontStyle(1, 16 + totalAddCount, 8, 16 + totalAddCount, 8, true, false, BaseLibrary.Common.UnderlineStyle.无下划线);
                excelHelper.MergeCells(1, 16 + totalAddCount, 5, 16 + totalAddCount, 7, "");
                #endregion
                #endregion

                #region 三 项目审核要点
                //审核情况
                excelHelper.SetCells(1, 20 + totalAddCount, 6, atObj.AuditCase.PD1);
                excelHelper.SetCells(1, 20 + totalAddCount, 8, atObj.AuditCase.PD1Opinion);
                excelHelper.SetCells(1, 21 + totalAddCount, 6, atObj.AuditCase.PD2);
                excelHelper.SetCells(1, 21 + totalAddCount, 8, atObj.AuditCase.PD2Opinion);
                #endregion

                #region 四 合同项下资料的汇总完整性
                excelHelper.SetCells(1, 24 + totalAddCount, 6, atObj.AuditCase.CD);
                excelHelper.SetCells(1, 24 + totalAddCount, 8, atObj.AuditCase.CDOpinion);

                #region 五 资本金到位情况
                excelHelper.SetCells(1, 27 + totalAddCount, 6, atObj.Amount_SumZBJ.ToString());
                excelHelper.SetCells(1, 27 + totalAddCount, 8, atObj.AuditCase.ZDOpinion);
                excelHelper.SetCells(1, 28 + totalAddCount, 6, atObj.AuditCase.ZD);
                excelHelper.SetCells(1, 29 + totalAddCount, 6, atObj.Amount_SumZBJ_Add.ToString());
                #endregion

                #region 六 该笔申请资金与项目总投资关系审核
                excelHelper.SetCells(1, 32 + totalAddCount, 6, atObj.Amount_SumGS.ToString());
                excelHelper.SetCells(1, 32 + totalAddCount, 8, atObj.AuditCase.ForPPriceOpinion);
                excelHelper.SetCells(1, 33 + totalAddCount, 6, atObj.Amount_SumCZLJ.ToString());
                excelHelper.SetCells(1, 34 + totalAddCount, 6, atObj.SummaryAmount.PP_GS);
                excelHelper.SetCells(1, 35 + totalAddCount, 7, string.Format("{0:P}", atObj.SummaryAmount.PP_GSResult));
                #endregion

                #region 七 与年度投资计划的关系审核
                startRowIndex = 38 + totalAddCount;
                curRowIndex = startRowIndex;
                excelHelper.SetCells(1, startRowIndex, 6, atObj.YearPlans[0].Amount.ToString());
                string planText = "";
                cellText = string.Format("本项目{0}年度投资计划 P1", atObj.YearPlans[0].TheYear);
                if (!string.IsNullOrEmpty(atObj.YearPlans[0].DocNo))
                {
                    cellText += string.Format("\n（以{0}号文件中的{1}年融资计划为准）", atObj.YearPlans[0].DocNo, atObj.YearPlans[0].TheYear);
                }
                planText = "P1";
                excelHelper.SetCells(1, startRowIndex, 3, cellText);
                for (int i = 1; i < atObj.YearPlans.Count; i++)
                {
                    excelHelper.InsertRows(1, curRowIndex + 1, 1);
                    excelHelper.SetCells(1, curRowIndex + 1, 2, (i + 1).ToString() + "、");
                    cellText = string.Format("本项目{0}年度投资计划 P{1}", atObj.YearPlans[i].TheYear, i + 1);
                    if (!string.IsNullOrEmpty(atObj.YearPlans[i].DocNo))
                    {
                        cellText += string.Format("\n（以{0}号文件中的{1}年融资计划为准）", atObj.YearPlans[i].DocNo, atObj.YearPlans[i].TheYear);
                    }
                    excelHelper.MergeCells(1, curRowIndex + 1, 3, curRowIndex + 1, 5, cellText, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                    excelHelper.MergeCells(1, curRowIndex + 1, 6, curRowIndex + 1, 7, atObj.YearPlans[i].Amount.ToString(), Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                    if (planText != "") { planText += "+"; }
                    planText += "P" + (i + 1).ToString();
                    curRowIndex++;
                    totalAddCount++;
                }
                excelHelper.SetCells(1, 39 + totalAddCount, 2, (atObj.YearPlans.Count + 1).ToString() + "、");
                excelHelper.SetCells(1, 39 + totalAddCount, 3, "累计年度投资计划 P=" + planText);
                excelHelper.SetCells(1, 39 + totalAddCount, 6, atObj.SummaryAmount.TAmount_Plan.ToString());
                excelHelper.SetCells(1, 40 + totalAddCount, 2, (atObj.YearPlans.Count + 2).ToString() + "、");
                excelHelper.SetCells(1, 40 + totalAddCount, 6, atObj.SummaryAmount.PP_Plan);
                excelHelper.SetCells(1, 41 + totalAddCount, 7, string.Format("{0:P}", atObj.SummaryAmount.PP_PlanResult));
                excelHelper.MergeCells(1, startRowIndex, 8, curRowIndex + 3, 8, atObj.AuditCase.ForPlanOpinion, Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter, Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter);
                #endregion

                #region 八 与批准贷款额度的关系审核
                //国开行贷款额度
                excelHelper.SetCells(1, 44 + totalAddCount, 6, atObj.Amount_SumGKLoan.ToString());
                excelHelper.SetCells(1, 44 + totalAddCount, 8, atObj.AuditCase.ForLoanOpinion);
                excelHelper.SetCells(1, 45 + totalAddCount, 6, atObj.SummaryAmount.PP_PGK);
                excelHelper.SetCells(1, 46 + totalAddCount, 7, string.Format("{0:P}", atObj.SummaryAmount.PP_PGKResult));
                excelHelper.SetCells(1, 47 + totalAddCount, 6, atObj.Amount_PSTK.ToString());
                excelHelper.SetCells(1, 48 + totalAddCount, 6, atObj.SummaryAmount.PP_PLoanTK);
                excelHelper.SetCells(1, 49 + totalAddCount, 7, string.Format("{0:P}", atObj.SummaryAmount.PP_PLoanTKResult));
                #endregion

                #region 九 与合同总价的关系审核
                excelHelper.SetCells(1, 52 + totalAddCount, 6, atObj.Amount_C_S.ToString());
                excelHelper.SetCells(1, 52 + totalAddCount, 8, atObj.AuditCase.ForCPriceOpinion);
                excelHelper.SetCells(1, 53 + totalAddCount, 6, atObj.TAmount_C_A_S.ToString());
                excelHelper.SetCells(1, 54 + totalAddCount, 7, string.Format("{0:P}", atObj.SummaryAmount.TAmount_C_P));
                excelHelper.SetCells(1, 55 + totalAddCount, 6, atObj.SummaryAmount.TAmount_C_T.ToString());
                excelHelper.SetCells(1, 56 + totalAddCount, 6, atObj.SummaryAmount.PP_CPrice);
                excelHelper.SetCells(1, 57 + totalAddCount, 7, string.Format("{0:P}", atObj.SummaryAmount.PP_CPriceResult));
                #endregion

                #region 十 与施工进度的关系审核
                excelHelper.SetCells(1, 60 + totalAddCount, 6, atObj.AuditCase.ForProgress);
                excelHelper.SetCells(1, 60 + totalAddCount, 8, atObj.AuditCase.ForProgressOpinion);
                #endregion

                #endregion

                //Response.Redirect(outFilePath);
                Response.Write("<script>alert('导出完成！');window.open('test2.aspx');</script>");
            }
            catch (Exception ex)
            {
                Response.Write("<script>alert('" + ex.Message + "');</script>");
            }
            finally
            {
                excelHelper.OutputExcelFile();
                excelHelper.Quit();
            }   
        }

        protected void btnPrintView_Click(object sender, EventArgs e)
        {
            string templetFile = Globals.Home + "Reports/F999-C001-V002.xlsx";

            Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            excelApp.Visible = false;

            excelApp.DisplayAlerts = false;
            excelApp.AlertBeforeOverwriting = false;

            object missing = System.Reflection.Missing.Value;
            //打开模板文件，得到WorkBook对象
            Microsoft.Office.Interop.Excel.Workbook workBook = excelApp.Workbooks.Open(Server.MapPath( templetFile), missing, missing, missing, missing, missing,
                missing, missing, missing, missing, missing, missing, missing, Type.Missing, Type.Missing);

            //得到WorkSheet对象
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);

          //生成模板实现打印预览以及打印
            //workBook.Close();
            //workBook.Protect(Type.Missing, true, true);
            //workSheet.Protect(missing, missing, missing, missing, true, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, false); //生成模板
            workSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait; //设置为纵向打印
            workSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;       //设置打印字为A4纸张
            workSheet.PrintPreview(true);            //打印预览

            excelApp.ActiveWorkbook.PrintOut(missing, missing, missing, missing, missing, missing, missing, missing);
            workBook.Close();
            excelApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excelApp);
            System.GC.Collect();


        }




        protected void btnToA_Click(object sender, EventArgs e)
        {
            try
            {
               string name=  AuditUtils.ExportToExcel(txtA.Text);
               Response.Write("<script>alert('导出A表成功！');window.open('" +  name + "');</script>");
            }
            catch (Exception ex)
            {
                Response.Write("<script>alert('" + ex.Message + "');</script>");
            }
        }

        protected void btnToB_Click(object sender, EventArgs e)
        {
            try
            {
                string name = AuditUtils.ExportToExcel(txtB.Text);
                Response.Write("<script>alert('导出B表成功！');window.open('" + name + "');</script>");
            }
            catch (Exception ex)
            {
                Response.Write("<script>alert('" + ex.Message + "');</script>");
            }
        }

        protected void btnToC_Click(object sender, EventArgs e)
        {
            try
            {
                string name = AuditUtils.ExportToExcel(txtC.Text);
                Response.Write("<script>alert('导出C表成功！');window.open('" + name + "');</script>");
            }
            catch (Exception ex)
            {
                Response.Write("<script>alert('" + ex.Message + "');</script>");
            }
        }
    }
}